package com.pacyki.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.pacyki.datamodel.PostCardBackground;
import com.pacyki.datamodel.PostCardText;
import com.pacyki.datamodel.SectionBackground;
import com.pacyki.datamodel.SectionText;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private Context mContext;
	
	private static final String DATABASE_NAME = "greeting_cards";
	
	/**
	 * Tags for "TABLE_NAME_SECTIONS_BACKGROUNDS" table.
	 */
	private static final String TABLE_NAME_SECTIONS_BACKGROUND = "SectionsBackgrounds";
	private static final String TABLE_NAME_SECTIONS_BACKGROUND_ITERATOR_ID = "_id";
	private static final String TABLE_NAME_SECTIONS_BACKGROUND_NAME = "name";
	
	/**
	 * Tags for "TABLE_NAME_SECTIONS_TEXTS" table.
	 */
	private static final String TABLE_NAME_SECTIONS_TEXT = "SectionTexts";
	private static final String TABLE_NAME_SECTIONS_TEXT_ITERATOR_ID = "_id";
	private static final String TABLE_NAME_SECTIONS_TEXT_NAME = "name";
	
	/**
	 * Tags for "TABLE_NAME_BACKGROUND" table.
	 */
	private static final String TABLE_NAME_BACKGROUND = "Background";
	private static final String TABLE_NAME_BACKGROUND_ITERATOR_ID = "_id";
	private static final String TABLE_NAME_BACKGROUND_SECTION_ID = "sectionId";
	private static final String TABLE_NAME_BACKGROUND_PATH = "path";
	private static final String TABLE_NAME_BACKGROUND_NAME = "name";
	
	/**
	 * Tags for "TABLE_NAME_TEXT" table.
	 */
	private static final String TABLE_NAME_TEXT = "text";
	private static final String TABLE_NAME_TEXT_ITERATOR_ID = "_id";
	private static final String TABLE_NAME_TEXT_SECTION_ID = "sectionId";
	private static final String TABLE_NAME_TEXT_PATH = "path";
	private static final String TABLE_NAME_TEXT_NAME = "name";
	private static final String TABLE_NAME_TEXT_BODY = "body";
	
	/**
	 * Tags for "TABLE_NAME_POST_CARD" table.
	 */
	private static final String TABLE_NAME_POST_CARD = "PostCard";
	private static final String TABLE_NAME_POST_CARD_ITERATOR_ID = "_id";
	private static final String TABLE_NAME_POST_CARD_BACKGROUND_ID = "backgraoundId";
	private static final String TABLE_NAME_POST_CARD_TEXT_ID = "textId";
	private static final String TABLE_NAME_POST_CARD_TEXT_SIZE = "textSize";
	private static final String TABLE_NAME_POST_CARD_TEXT_POSITION = "textPosition";
	private static final String TABLE_NAME_POST_CARD_RECIPIENTS = "recipients";
	
	/**
	 * Tags for "TABLE_NAME_RECIPIENT" table.
	 */
	private static final String TABLE_NAME_RECIPIENT = "Recipient";
	private static final String TABLE_NAME_RECIPIENT_ITERATOR_ID = "_id";
	private static final String TABLE_NAME_RECIPIENT_EMAIL = "eMail";
	private static final String TABLE_NAME_RECIPIENT_NAME = "name";
	
	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, 1);
		mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String createSectionsBackground = "CREATE TABLE " + TABLE_NAME_SECTIONS_BACKGROUND + 
									" (" +
									TABLE_NAME_SECTIONS_BACKGROUND_ITERATOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
									TABLE_NAME_SECTIONS_BACKGROUND_NAME + " TEXT)";
		db.execSQL(createSectionsBackground);
		
		String createSectionsText = "CREATE TABLE " + TABLE_NAME_SECTIONS_TEXT + 
									" (" +
									TABLE_NAME_SECTIONS_TEXT_ITERATOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
									TABLE_NAME_SECTIONS_TEXT_NAME + " TEXT)";
		db.execSQL(createSectionsText);

		String createBackground = "CREATE TABLE " + TABLE_NAME_BACKGROUND + 
									" (" +
									TABLE_NAME_BACKGROUND_ITERATOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
									TABLE_NAME_BACKGROUND_SECTION_ID + "TEXT, " +
									TABLE_NAME_BACKGROUND_PATH + "TEXT, " +
									TABLE_NAME_BACKGROUND_NAME + " TEXT)";
		db.execSQL(createBackground);

		String createText = "CREATE TABLE " + TABLE_NAME_TEXT + 
									" (" +
									TABLE_NAME_TEXT_ITERATOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
									TABLE_NAME_TEXT_SECTION_ID + "TEXT, " +
									TABLE_NAME_TEXT_PATH + "TEXT, " +
									TABLE_NAME_TEXT_NAME + "TEXT, " +
									TABLE_NAME_TEXT_BODY + " TEXT)";
		db.execSQL(createText);
		
		String createPostCard = "CREATE TABLE " + TABLE_NAME_POST_CARD + 
									" (" +
									TABLE_NAME_POST_CARD_ITERATOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
									TABLE_NAME_POST_CARD_BACKGROUND_ID + "TEXT, " +
									TABLE_NAME_POST_CARD_TEXT_ID + "TEXT, " +
									TABLE_NAME_POST_CARD_TEXT_SIZE + "TEXT, " +
									TABLE_NAME_POST_CARD_TEXT_POSITION + "TEXT, " +
									TABLE_NAME_POST_CARD_RECIPIENTS + " TEXT)";
		db.execSQL(createPostCard);
		
		String createRecipient = "CREATE TABLE " + TABLE_NAME_RECIPIENT + 
									" (" +
									TABLE_NAME_RECIPIENT_ITERATOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
									TABLE_NAME_RECIPIENT_EMAIL + "TEXT, " +
									TABLE_NAME_RECIPIENT_NAME + " TEXT)";
		db.execSQL(createRecipient);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_SECTIONS_BACKGROUND);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_SECTIONS_TEXT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_BACKGROUND);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_TEXT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_POST_CARD);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_RECIPIENT);

		onCreate(db);
	}

	/**
	 * Functions for "TABLE_NAME_SECTIONS_BACKGROUND" table.
	 */	
	public void putSectionsBacground(ArrayList<SectionBackground> sectionsBackground){
		if(sectionsBackground != null) {
			if (sectionsBackground.size() > 0) {
				SQLiteDatabase db = this.getWritableDatabase();
				db.beginTransaction();
				for (SectionBackground sectionItem : sectionsBackground) {
					db.insert(TABLE_NAME_SECTIONS_BACKGROUND, null, sectionBackgroundItemToContentValues(sectionItem));
				}
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
	}
	private ContentValues sectionBackgroundItemToContentValues(SectionBackground sectionItem) {	
		ContentValues values = new ContentValues();
		if(sectionItem.getIterator() != null) {
			values.put(TABLE_NAME_SECTIONS_BACKGROUND_ITERATOR_ID, sectionItem.getIterator());
		} else {
			values.put(TABLE_NAME_SECTIONS_BACKGROUND_ITERATOR_ID, "");
		}
		
		if(sectionItem.getName() != null) {
			values.put(TABLE_NAME_SECTIONS_BACKGROUND_NAME, sectionItem.getName());
		} else {
			values.put(TABLE_NAME_SECTIONS_BACKGROUND_NAME, "");
		}
		
		return values;
	}
	private SectionBackground cursorToSectionBackground(Cursor cursor) {
		SectionBackground sectionItem = new SectionBackground();
		sectionItem.setIterator(cursor.getString(cursor.getColumnIndex(TABLE_NAME_SECTIONS_BACKGROUND_ITERATOR_ID)));
		sectionItem.setName(cursor.getString(cursor.getColumnIndex(TABLE_NAME_SECTIONS_BACKGROUND_NAME)));
		return sectionItem;
	}
	public ArrayList<SectionBackground> getSectionsBackground() {
		Cursor cursor = getSectionsBackgroundCursor();
		ArrayList<SectionBackground> sectionItems = new ArrayList<SectionBackground>();
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			do {
				SectionBackground sectionItem = cursorToSectionBackground(cursor);
				sectionItems.add(sectionItem);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return sectionItems;
	}
	private Cursor getSectionsBackgroundCursor() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME_SECTIONS_BACKGROUND, new String[] { TABLE_NAME_SECTIONS_BACKGROUND_ITERATOR_ID, TABLE_NAME_SECTIONS_BACKGROUND_NAME}, null, null, null, null, null);
		return cursor;
	}	
	
	/**
	 * Functions for "TABLE_NAME_SECTIONS_TEXT" table.
	 */	
	public void putSectionsText(ArrayList<SectionText> sectionsText){
		if(sectionsText != null) {
			if (sectionsText.size() > 0) {
				SQLiteDatabase db = this.getWritableDatabase();
				db.beginTransaction();
				for (SectionText sectionItem : sectionsText) {
					db.insert(TABLE_NAME_SECTIONS_TEXT, null, sectionTextItemToContentValues(sectionItem));
				}
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
	}
	private ContentValues sectionTextItemToContentValues(SectionText sectionItem) {	
		ContentValues values = new ContentValues();
		if(sectionItem.getIterator() != null) {
			values.put(TABLE_NAME_SECTIONS_TEXT_ITERATOR_ID, sectionItem.getIterator());
		} else {
			values.put(TABLE_NAME_SECTIONS_TEXT_ITERATOR_ID, "");
		}
		
		if(sectionItem.getName() != null) {
			values.put(TABLE_NAME_SECTIONS_TEXT_NAME, sectionItem.getName());
		} else {
			values.put(TABLE_NAME_SECTIONS_TEXT_NAME, "");
		}
		
		return values;
	}
	private SectionText cursorToSectionText(Cursor cursor) {
		SectionText sectionItem = new SectionText();
		sectionItem.setIterator(cursor.getString(cursor.getColumnIndex(TABLE_NAME_SECTIONS_TEXT_ITERATOR_ID)));
		sectionItem.setName(cursor.getString(cursor.getColumnIndex(TABLE_NAME_SECTIONS_TEXT_NAME)));
		return sectionItem;
	}
	public ArrayList<SectionText> getSectionsText() {
		Cursor cursor = getSectionsTextCursor();
		ArrayList<SectionText> sectionItems = new ArrayList<SectionText>();
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			do {
				SectionText sectionItem = cursorToSectionText(cursor);
				sectionItems.add(sectionItem);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return sectionItems;
	}
	private Cursor getSectionsTextCursor() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME_SECTIONS_TEXT, new String[] { TABLE_NAME_SECTIONS_TEXT_ITERATOR_ID, TABLE_NAME_SECTIONS_TEXT_NAME}, null, null, null, null, null);
		return cursor;
	}	
	
	/**
	 * Functions for "TABLE_NAME_BACKGROUND" table.
	 */	
	public void putPostCardBackgrounds(ArrayList<PostCardBackground> backgrounds){
		if(backgrounds != null) {
			if (backgrounds.size() > 0) {
				SQLiteDatabase db = this.getWritableDatabase();
				db.beginTransaction();
				for (PostCardBackground backgroundItem : backgrounds) {
					db.insert(TABLE_NAME_BACKGROUND, null, postCardBackgroundItemToContentValues(backgroundItem));
				}
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
	}
	private ContentValues postCardBackgroundItemToContentValues(PostCardBackground backgroundItem) {	
		ContentValues values = new ContentValues();
		if(backgroundItem.getIterator() != null) {
			values.put(TABLE_NAME_BACKGROUND_ITERATOR_ID, backgroundItem.getIterator());
		} else {
			values.put(TABLE_NAME_BACKGROUND_ITERATOR_ID, "");
		}
		
		if(backgroundItem.getName() != null) {
			values.put(TABLE_NAME_BACKGROUND_NAME, backgroundItem.getName());
		} else {
			values.put(TABLE_NAME_BACKGROUND_NAME, "");
		}
		
		if(backgroundItem.getSectionId() != null) {
			values.put(TABLE_NAME_BACKGROUND_SECTION_ID, backgroundItem.getSectionId());
		} else {
			values.put(TABLE_NAME_BACKGROUND_SECTION_ID, "");
		}
		
		if(backgroundItem.getPath() != null) {
			values.put(TABLE_NAME_BACKGROUND_PATH, backgroundItem.getPath());
		} else {
			values.put(TABLE_NAME_BACKGROUND_PATH, "");
		}
		
		if(backgroundItem.getName() != null) {
			values.put(TABLE_NAME_BACKGROUND_NAME, backgroundItem.getName());
		} else {
			values.put(TABLE_NAME_BACKGROUND_NAME, "");
		}
		
		return values;
	}
	private PostCardBackground cursorToPostCardBackGround(Cursor cursor) {
		PostCardBackground backgroundItem = new PostCardBackground();
		backgroundItem.setIterator(cursor.getString(cursor.getColumnIndex(TABLE_NAME_BACKGROUND_ITERATOR_ID)));
		backgroundItem.setSectionId(cursor.getString(cursor.getColumnIndex(TABLE_NAME_BACKGROUND_SECTION_ID)));
		backgroundItem.setPath(cursor.getString(cursor.getColumnIndex(TABLE_NAME_BACKGROUND_PATH)));
		backgroundItem.setName(cursor.getString(cursor.getColumnIndex(TABLE_NAME_BACKGROUND_NAME)));
		return backgroundItem;
	}
	public ArrayList<PostCardBackground> getPostCardBackGrounds() {
		Cursor cursor = getPostCardBackgroundsCursor();
		ArrayList<PostCardBackground> backgroundItems = new ArrayList<PostCardBackground>();
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			do {
				PostCardBackground backgroundItem = cursorToPostCardBackGround(cursor);
				backgroundItems.add(backgroundItem);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return backgroundItems;
	}
	private Cursor getPostCardBackgroundsCursor() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME_BACKGROUND, new String[] { TABLE_NAME_BACKGROUND_ITERATOR_ID, TABLE_NAME_BACKGROUND_SECTION_ID, TABLE_NAME_BACKGROUND_PATH, TABLE_NAME_BACKGROUND_NAME}, null, null, null, null, null);
		return cursor;
	}	

	
	/**
	 * Functions for "TABLE_NAME_TEXT" table.
	 */	
	public void putPostCardTexts(ArrayList<PostCardText> texts){
		if(texts != null) {
			if (texts.size() > 0) {
				SQLiteDatabase db = this.getWritableDatabase();
				db.beginTransaction();
				for (PostCardText textItem : texts) {
					db.insert(TABLE_NAME_TEXT, null, postCardTextItemToContentValues(textItem));
				}
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
	}
	private ContentValues postCardTextItemToContentValues(PostCardText textItem) {	
		ContentValues values = new ContentValues();
		if(textItem.getIterator() != null) {
			values.put(TABLE_NAME_TEXT_ITERATOR_ID, textItem.getIterator());
		} else {
			values.put(TABLE_NAME_TEXT_ITERATOR_ID, "");
		}
		
		if(textItem.getName() != null) {
			values.put(TABLE_NAME_TEXT_NAME, textItem.getName());
		} else {
			values.put(TABLE_NAME_TEXT_NAME, "");
		}
		
		if(textItem.getSectionId() != null) {
			values.put(TABLE_NAME_TEXT_SECTION_ID, textItem.getSectionId());
		} else {
			values.put(TABLE_NAME_TEXT_SECTION_ID, "");
		}
		
		if(textItem.getPath() != null) {
			values.put(TABLE_NAME_TEXT_PATH, textItem.getPath());
		} else {
			values.put(TABLE_NAME_TEXT_PATH, "");
		}
		
		if(textItem.getName() != null) {
			values.put(TABLE_NAME_TEXT_NAME, textItem.getName());
		} else {
			values.put(TABLE_NAME_TEXT_NAME, "");
		}
		
		if(textItem.getBody() != null) {
			values.put(TABLE_NAME_TEXT_BODY, textItem.getBody());
		} else {
			values.put(TABLE_NAME_TEXT_BODY, "");
		}
		
		return values;
	}
	private PostCardText cursorToPostCardText(Cursor cursor) {
		PostCardText textItem = new PostCardText();
		textItem.setIterator(cursor.getString(cursor.getColumnIndex(TABLE_NAME_TEXT_ITERATOR_ID)));
		textItem.setSectionId(cursor.getString(cursor.getColumnIndex(TABLE_NAME_TEXT_SECTION_ID)));
		textItem.setPath(cursor.getString(cursor.getColumnIndex(TABLE_NAME_TEXT_PATH)));
		textItem.setName(cursor.getString(cursor.getColumnIndex(TABLE_NAME_TEXT_NAME)));
		textItem.setBody(cursor.getString(cursor.getColumnIndex(TABLE_NAME_TEXT_BODY)));
		return textItem;
	}
	public ArrayList<PostCardText> getPostCardTexts() {
		Cursor cursor = getPostCardTextsCursor();
		ArrayList<PostCardText> textItems = new ArrayList<PostCardText>();
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			do {
				PostCardText textItem = cursorToPostCardText(cursor);
				textItems.add(textItem);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return textItems;
	}
	private Cursor getPostCardTextsCursor() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME_TEXT, new String[] { TABLE_NAME_TEXT_ITERATOR_ID, TABLE_NAME_TEXT_SECTION_ID, TABLE_NAME_TEXT_PATH, TABLE_NAME_TEXT_NAME, TABLE_NAME_TEXT_BODY}, null, null, null, null, null);
		return cursor;
	}	
}